<?php
/**
 * 用户统计Service
 * User: crstu
 * Date: 2016/6/20
 * Time: 14:20
 */

namespace backend\models\Service;


use common\models\DAO\User;
use common\models\Service\StatsService;

class UserStatsService extends StatsService
{

    /*
     * 计算现在用户数量
     */
    public static function countUserNow(){
        return self::sumAndCountByField('c', 'user', 'id', [['=', 'status', 1]]);
    }

    /*
     * 计算已认证用户数
     */
    public static function countUserIdentity(){
        return self::sumAndCountByField('c', 'user', 'id', [['=', 'status', 1], ['!=', 'identity', '']]);
    }

    /*
     * 计算已关注用户数量
     */
    public static function countUserWeixin(){
        return self::sumAndCountByField('c', 'user', 'id', [['=', 'status', 1], ['!=', 'openID', '']]);
    }

    /**
     * 根据时间计算用户新增人数
     */
    public static function countUserGrowByTime($time = 'today'){
        $User = new User();
        if($time == 'today'){
            $today = strtotime(date('Y-m-d 0:0:0')); //当天
            $total = $User->extCount("createTime >= $today");
        }elseif($time == 'this-week'){
            $this_week  = strtotime("+0 week Monday"); //本周
            $total = $User->extCount("createTime >= $this_week");
        }elseif($time == 'last-week'){
            $last_week  = strtotime("-7 day"); //最近7天
            $total = $User->extCount("createTime >= $last_week");
        }elseif($time == 'this-month'){
            $this_month = strtotime(date('Y-m-1 0:0:0')); //本月
            $total = $User->extCount("createTime >= $this_month");
        }elseif($time == 'last-month'){
            $last_month  = strtotime("-30 day"); //最近30天
            $total = $User->extCount("createTime >= $last_month");
        }else{
            $total = -1;
        }
        return (int)$total;
    }

    /*
     * 根据时间段计算增长用户数
     * @param int|array $start_time 数组形式则为时间段，否则为开始时间
     * @param int $end_time
     */
    public static function getUserAddArrayByTime($start_time, $end_time = null){
        if(is_array($start_time)){
            return self::statsByTimeArray('c', 'user', 'id', $start_time);
        }else{
            return self::statsByTimePeriod('c', 'user', 'id', $start_time, $end_time);
        }
    }

    /*
     * 根据时间统计用户总数
     * @param int $end_time
     */
    public static function getUserTotalArrayByTime($end_time = null){
        if(is_array($end_time)){
            $data = [];
            foreach($end_time as $t){
                $t += 86400;
                $data[] = self::statsByTimePeriod('c', 'user', 'id', 0, $t);
            }
            return $data;
        }else{
            $end_time += 86400;
            return self::statsByTimePeriod('c', 'user', 'id', 0, $end_time);
        }
    }

    /*
     * 根据性别计算用户百分比
     */
    public static function calUserSexPercent($start_time = 0, $end_time = 0){

        $result = self::countGroupByFieldByTime('user', 'sex', $start_time, $end_time);

        if(!empty($result) && count($result)>=3){
            $data['未定义'] = $result[0];
            $data['男性'] = $result[1];
            $data['女性'] = $result[2];
            return $data;
        }
        return false;
    }

    /**
     * 全平台用户的年龄段分布
     * todo 暂时未加时间参数
     * 0~20~30~40~50~60~80
     * @param $start_time
     * @param $end_time
     */
    public static function calUserAgePercent($start_time = 0, $end_time = 0){
        $sql = "
            select (
                case when age<=20 then '<20'
                when age>20 and age <=30 then '20-30'
                when age>30 and age <=40 then '30-40'
                when age>40 and age <=50 then '40-50'
                when age>50 and age <=60 then '50-60'
                when age>60 and age <=80 then '60-80'
                when age>80 then '>80' end
            ) as ageGroup, count(id) as totalNumber
            from {{%user}}
            group by (
                case when age<=20 then '<20'
                when age>20 and age <=30 then '20-30'
                when age>30 and age <=40 then '30-40'
                when age>40 and age <=50 then '40-50'
                when age>50 and age <=60 then '50-60'
                when age>60 and age <=80 then '60-80'
                when age>80 then '>80' end
            )
        ";
        $db = \Yii::$app->db;
        $res = $db->createCommand($sql)->queryAll();
        $data = [];
        if(!empty($res)){
            foreach($res as $k => $v){
                if(strtoupper($v['ageGroup']) == 'NULL' || $v['ageGroup'] == null){
                    $v['ageGroup'] = '未填写年龄';
                }
                $data[$v['ageGroup']] = $v['totalNumber'];
            }
        }
        return $data;
    }


    /**
     * 计算用户充值的年龄段分布
     */
    public static function calUserChargeByAge(){
        $sql['<20'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>0 AND user.age<=20";
        $sql['20~30'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>20 AND user.age<=30";
        $sql['30~40'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>30 AND user.age<=40";
        $sql['40~50'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>40 AND user.age<=50";
        $sql['50~60'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>50 AND user.age<=60";
        $sql['60~80'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>60 AND user.age<=80";
        $sql['>80'] = "SELECT SUM(money) FROM {{%user_charge}} AS charge LEFT JOIN {{%user}} AS user ON user.id = charge.userID WHERE charge.`status`=1 AND user.age>80";
        $db = \Yii::$app->db;
        $data = [];
        foreach($sql as $k => $s){
            $res = $db->createCommand($s)->queryOne();
            $data[$k] = intval($res['SUM(money)']/100);
        }
        return $data;
    }

    /*
     * 计算认证用户和非认证用户百分比
     */
    public static function calUserIdentityPercent($start_time = 0, $end_time = 0){

        $result[0] = self::sumAndCountByField('c', 'user', 'id', [['=', 'identity', '']]);
        $result[1] = self::sumAndCountByField('c', 'user', 'id', [['!=', 'identity', '']]);

        if(!empty($result)){
            $data['未认证'] = $result[0];
            $data['已认证'] = $result[1];
            return $data;
        }
        return false;
    }

    /*
     * 统计用户地区排行
     */
    public static function rankUserGroupByProvince(){
        return self::countGroupByField('user', 'province', [['=', 'status', 1]]);
    }

    /**
     * 提现充值比
     * @param int $ratio 比例
     * @param int $start_time
     * @param int $end_time
     * @return array
     */
    public static function cashChargeRatio($ratio, $start_time = 0, $end_time = 0)
    {

        if($start_time > 0 && $end_time > 0){
            $where = 'AND createTime > ' . $start_time . ' AND ' . 'createTime < ' . $end_time;
        }elseif($end_time > 0){
            $where = 'AND createTime > ' . $end_time;
        }elseif($start_time > 0){
            $where = 'AND createTime > ' . $start_time;
        }else{
            $where = '';
        }

        $sql_charge = '(SELECT SUM(money) FROM {{%user_charge}} WHERE userID=user.id '.$where.')';
        $sql_cash   = '(SELECT SUM(money) FROM {{%user_cash}} WHERE userID=user.id '.$where.')';

        $sql = 'SELECT user.id, user.realName, user.createTime,
                '.$sql_charge.' AS charge, '.$sql_cash.' AS cash,
                ('.$sql_cash.'/'.$sql_charge.') AS ratio FROM {{%user}} AS user
                WHERE ('.$sql_cash.'/'.$sql_charge.')>'.$ratio.'
                ORDER BY '.$sql_cash.'/'.$sql_charge.' DESC';

        return \Yii::$app->db->createCommand($sql)->queryAll();

    }

    /**
     * 统计用户地区数量
     * @param int $start_time
     * @param int $end_time
     * @return array
     */
    public static function statUserNumberByProvince($start_time = 0, $end_time = 0)
    {
        //$provinces = ['北京','天津','安徽','上海','浙江','福建','重庆','江西','山东','河南','内蒙古','湖北','新疆','湖南','宁夏','广东','西藏','海南','广西','四川','河北','贵州','山西','云南','辽宁','陕西','吉林','甘肃','黑龙江','青海','江苏','香港','澳门','台湾'];
        $provinces = '北京,天津,安徽,上海,浙江,福建,重庆,江西,山东,河南,内蒙古,湖北,新疆,湖南,宁夏,广东,西藏,海南,广西,四川,河北,贵州,山西,云南,辽宁,陕西,吉林,甘肃,黑龙江,青海,江苏,香港,澳门,台湾';
        $data = self::countGroupByFieldByTime('user', 'province', $start_time, $end_time, 'createTime');
        foreach($data as $k => $v){
            if(!strstr($provinces, $k)){
                unset($data[$k]);
            }
        }
        return $data;

    }

}